from django.shortcuts import render

# Create your views here.



from django.http import JsonResponse
from .models import ClassroomMember, StudentScore
from rest_framework.response import Response
from .models import ActivityLearnLog
import json
from django.db.models import Sum
from django.db.models import ExpressionWrapper, F, IntegerField, CharField
from django.db.models.functions import Cast
from flask import Flask, request, jsonify
import pandas as pd
from sklearn.linear_model import LinearRegression

#班级编号
def get_classroom_members(request):
    classroom_members = ClassroomMember.objects.values('classroomId', 'userId', 'role').order_by('classroomId', 'userId')

    classroom_members = list(classroom_members)

    return JsonResponse({
        "code": 200,
        "msg": {
            "data": classroom_members,
            "status": 'ok'
        }

    })

#班级成员表--每个班级的学生\

def extract_data_by_classroom(request):

    id = json.loads(request.GET.get('classroomId'))
    classroom_members = ClassroomMember.objects.filter(classroomId=id, role='|student|').values('classroomId', 'userId', 'levelId',
                                                                              'noteNum', 'learnedNum').order_by('userId')
    classroom_members = list(classroom_members)
    return JsonResponse({
        "code": 200,
         "msg": {
             "data": classroom_members,
             "status": 'ok'
         }

    })


#学生学习时长
def get_time(request):
    # 使用annotate来聚合相同userId和courseTaskId的watchingTime
    # 假设data字段是一个JSONField，并且可以直接查询其中的watchingTime
    # 如果data不是JSONField，你可能需要使用其他方法来解析text字段
    if request.method == "GET":
        id = json.loads(request.GET.get('id'))
        watching_times = ActivityLearnLog.objects.filter(
            event='watching', userId=id
        ).annotate(
            # 将data字段解析为JSON，并尝试转换为整数
            watching_time_json=ExpressionWrapper(
                Cast(F('data'), output_field=CharField()),
                output_field=IntegerField()
            )
        ).values('userId', 'courseTaskId').annotate(
            watchingTime=Sum('watching_time_json')
        )

        # 将查询结果转换为JSON格式
        json_data1 = [
                item['courseTaskId']
            for item in watching_times
        ]
        json_data2 = [
                item['watchingTime']
            for item in watching_times
        ]

        # 返回JSON响应
        return JsonResponse({
            "code": 200,
            "msg": {
                "data_x": json_data1,
                "data_y": json_data2,
                "status": "ok"
            }
        })

#学生分数——等级
def score_grade(request):
    # 查询所有成绩
    if request.method == "GET":
        id = json.loads(request.GET.get('id'))
        scores = StudentScore.objects.filter(
            userId = id
        ).all()

        # 成绩等级判断
        grades = []
        for score in scores:
            if score.score >= 80:
                grade = '学习成绩优异'
            elif score.score >= 60:
                grade = '良好成绩有待提升'
            else:
                grade = '成绩较低需关注'
            grades.append({
                'courseId': score.courseId,
                'score': score.score,
                'grade': grade
            })
            print(grades)
        # 返回JSON响应
        return JsonResponse({
            "code": 200,
            "msg": {
                "data": grades,
                "status": "ok"
            }
        })

#线性拟合
#获取观看时长
def get_learning_time(request):
    # 使用annotate来聚合相同userId和courseTaskId的watchingTime
    watching_times = ActivityLearnLog.objects.filter(event='watching').annotate(
        # 将data字段解析为JSON，并尝试转换为整数
        watching_time_json=ExpressionWrapper(
            Cast(F('data'), output_field=CharField()), output_field=IntegerField()
        )
    ).values('userId', 'courseTaskId').annotate(
        watchingTime=Sum('watching_time_json')
    )

    # 将查询结果直接转换为DataFrame
    df_learning_time = pd.DataFrame(list(watching_times))

    return df_learning_time

#获取成绩

def get_student_scores(request):
    # 查询所有成绩
    scores = StudentScore.objects.all()
    # 成绩等级判断
    grades = [
        {
            'userId': score.userId,
            'courseTaskId': score.courseTaskId,
            'score': score.score,
            'grade': '学习成绩优异' if score.score >= 80 else '良好成绩有待提升' if score.score >= 60 else '成绩较低需关注'
        }
        for score in scores
    ]
    df_student_scores = pd.DataFrame(grades)

    return df_student_scores
#线性拟合
def correlation_analysis(request):
    try:
        # 获取学习时长数据
        learning_time_data = get_learning_time(request)
        # 获取学生成绩数据
        student_scores_data = get_student_scores(request)

        # 将数据转换为Pandas DataFrame
        df_combined = learning_time_data.merge(student_scores_data, on=['userId', 'courseTaskId'])

        # 计算相关性系数
        # 这里我们使用简单的线性回归来演示，实际上你可能需要更复杂的相关性分析
        X = df_combined[['watchingTime']]  # 学习时长作为特征
        y = df_combined['score']  # 成绩作为目标变量
        model = LinearRegression()
        model.fit(X, y)
        correlation_coefficient = model.coef_[0]  # 获取斜率作为相关性系数的代理

        # 返回结果
        return JsonResponse({'correlation_coefficient': correlation_coefficient})
    except Exception as e:
        return JsonResponse({'error': str(e)}, status=500)


